#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2019/7/29 下午4:23
# @Author  : fugang_le
# @Software: PyCharm



class Xlsx_():
    def read_xlsx_1(self):
        from openpyxl import load_workbook
        file = ''
        work_book = load_workbook(file)
        book_sheet = work_book[work_book.sheetnames[0]]

        rows = book_sheet.rows
        columns = book_sheet.columns

        lines = []
        for row in rows:
            line = [col.value for col in row]
            lines.append(line)
        return lines

    def read_xlsx_2(self):
        import xlrd
        file = ''
        data = xlrd.open_workbook(file)
        table = data.sheets()[0]  # 通过索引顺序获取
        nrows = table.nrows  # 获取行数

        for i in range(nrows):
            if i == 0:
                continue
            item = table.row_values(i)
            for i in item:
                print(i)

    def write_xlsx_1(self):
        import xlsxwriter as wx
        title = ['a', 'b', 'c', 'd', 'e', 'f']
        wb = wx.Workbook('XXXX.xlsx')
        worksheet = wb.add_worksheet()
        for i in range(0, len(title)):
            worksheet.write(0, i, title[i])

        nrow = 1        # 行号
        content = ''    # 内容
        col = 9         # 列数
        for i in range(0, col):
            worksheet.write(nrow, i, content)
            nrow += 1


import pandas as pd
class Csv_operate:

    def write_csv(self):
        contents = []
        labels = []
        df_data = pd.DataFrame({'content': contents, 'label': labels})
        ''' encoding=’utf_8_sig :to_csv函数输出的utf8数据用Excel打开是乱码, 微软产品能正确识别utf-8带bom格式，所以编码要设置
        成utf-8带bom格式
        quoting=1：内容用双引号隔开'''
        df_data.to_csv('pd.csv', index=False, quoting=1, encoding='utf_8_sig')

    def read_csv(self):
        csv_file = ''
        data = pd.read_csv(csv_file)

        for index, row in data.iterrows():
            a = row['content']
            b = row['label']



class ExcelOperate:
    def _write(self):
        # 写
        dic1 = {'标题列1': ['张三', '李四'],
                '标题列2': [80, 90]
                }
        df = pd.DataFrame(dic1)
        df.to_excel('1.xlsx', index=False)

    # 多个sheet写入一个excel文件中
    def _wirte_many_sheet(self):
        dic1 = {'标题列1': ['张三', '李四'],
                '标题列2': [80, 90]
                }
        df1 = pd.DataFrame(dic1)
        df2 = df1.copy()
        with pd.ExcelWriter(r'D:\myExcel\res.xlsx') as writer:
            df1.to_excel(writer, sheet_name='Sheet_name_1')
            df2.to_excel(writer, sheet_name='Sheet_name_2')

    def _read(self):
        # 读
        data = pd.read_excel('1.xlsx')

        # 查看所有的值
        print(data.values)

        # 查看第一行的值
        print(data.values[0])

        # 查看某一列所有的值
        print(data['标题列1'].values)

        # 新增列
        data['标题列3'] = None

        # 新增行
        data.loc[3] = ['王五', 100, '男']

        # 删除行：axis=0
        data = data.drop([0, 1], axis=0)

        # 删除列：axis=1
        data.drop('标题列3', axis=1)

        # 保存
        pd.DataFrame(data).to_excel('1.xlsx', sheet_name='Sheet1', index=False, header=True)

        '''
        复制代码
        pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, 
                      usecols=None, squeeze=False,dtype=None, engine=None, 
                      converters=None, true_values=None, false_values=None, 
                      skiprows=None, nrows=None, na_values=None, parse_dates=False, 
                      date_parser=None, thousands=None, comment=None, skipfooter=0, 
                      convert_float=True, **kwds)
        复制代码
        　　io：excel文件
        
        　　sheet_name：返回指定sheet，默认索引0返回第一个，也可用名称，如果返回多个则可用列表，为None则返回全表
        
        　　header：指定表头，也可用列表指定多行
        
        　　names：自定义列名，长度和Excel列长度必须一致
        
        　　index_col：用作索引的列
        
        　　usecols：读取指定的列，参数为列表，如[0,1]表示第1和第2列
        '''


######################################### 流式读取数G超大文件 #########################################
from functools import partial
# 每次只读取8kb返回

def read_from_file(filename, block_size = 1024 * 8):
    with open(filename, "r") as fp:
        for chunk in iter(partial(fp.read, block_size), ""):
            yield chunk
######################################### 流式读取数G超大文件 #########################################